package com.yinkgh.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.WorkbookUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.microsoft.schemas.office.visio.x2012.main.CellType;
import com.yinkgh.mybatis.view.BondTrialInfoView;


/**
 * excel工具类
 * @author yinlong
 * @param <T>
 *
 */
public class ExcelUtil  {
	
	
	/**xls格式文件*/
	public static final String  TYPE_XLS = ".xls";
	
	/**xlsx格式文件*/
	public static final String  TYPE_XLSX = ".xlsx";
	/**分类cell样式*/
//	private static CellStyle CLASSIFY_CELLSTYLE = null;
	/**title样式*/
//	private static CellStyle TITLE_CELLSTYLE = null;
	/**ContentCellStyle*/
//	private static CellStyle CONTENT_CELLSTYLE = null;
	
	private static class LazyHolder {
		private static final ExcelUtil EXCEL_UTIL = new ExcelUtil();
	}
	
	private ExcelUtil() {
	}
	
	/**
	 * 实例化当前工具类
	 * @return
	 */
	public static final ExcelUtil getInstance() {
		return LazyHolder.EXCEL_UTIL;
	}
	
	
	/**
	 * 设置分类cell样式
	 * @param workbook
	 * @return
	 */
	private CellStyle getClassifyCellStyle(Workbook workbook, CellStyle classifyCellStyle) {
    	   classifyCellStyle = workbook.createCellStyle();
    	 //设置样式
           Font font = workbook.createFont();
           font.setBoldweight(Font.BOLDWEIGHT_BOLD);//设置粗体
           font.setFontHeightInPoints((short)11); //字体大小
           font.setFontName("微软雅黑");
//	                   font.setFontName("楷体");
           
//	                   CellStyle cellStyle = workbook.createCellStyle();
           classifyCellStyle.setAlignment(CellStyle.ALIGN_CENTER); 
           classifyCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中
           classifyCellStyle.setFont(font);
           classifyCellStyle.setBorderBottom(CellStyle.BORDER_THIN); // 底部边框
           classifyCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 底部边框颜色
            
           classifyCellStyle.setBorderLeft(CellStyle.BORDER_THIN);  // 左边边框
//	                   classifyCellStyle.setLeftBorderColor(IndexedColors.GREY_25_PERCENT.getIndex()); // 左边边框颜色
           classifyCellStyle.setLeftBorderColor(IndexedColors.GREY_25_PERCENT.getIndex()); // 左边边框颜色
            
           classifyCellStyle.setBorderRight(CellStyle.BORDER_THIN); // 右边边框
           classifyCellStyle.setRightBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());  // 右边边框颜色
            
           classifyCellStyle.setBorderTop(CellStyle.BORDER_THIN); // 上边边框
           classifyCellStyle.setTopBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());  // 上边边框颜色
           
           classifyCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); // 前景色
           classifyCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); 
//	                   classifyCellStyle.setFillPattern(CellStyle.BORDER_SLANTED_DASH_DOT); 
        return classifyCellStyle;
	
	}
	
	/**
	 * 设置title样式
	 * @param workbook
	 * @return
	 */
	private CellStyle getTitleCellStyle(Workbook workbook, CellStyle titleCellStyle) {
		titleCellStyle = workbook.createCellStyle();
    	 //设置样式
       Font font = workbook.createFont();
       font.setBoldweight(Font.BOLDWEIGHT_BOLD);//设置粗体
       font.setFontHeightInPoints((short)11); //字体大小
//	                   font.setFontName("微软雅黑");
       font.setFontName("楷体");
       
//	                   CellStyle cellStyle = workbook.createCellStyle();
       titleCellStyle.setAlignment(CellStyle.ALIGN_CENTER);//水平居中
       titleCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中
       titleCellStyle.setFont(font);
       titleCellStyle.setBorderBottom(CellStyle.BORDER_THIN); // 底部边框
       titleCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 底部边框颜色
        
       titleCellStyle.setBorderLeft(CellStyle.BORDER_THIN);  // 左边边框
//	                   titleCellStyle.setLeftBorderColor(IndexedColors.GREY_25_PERCENT.getIndex()); // 左边边框颜色
       titleCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边边框颜色
        
       titleCellStyle.setBorderRight(CellStyle.BORDER_THIN); // 右边边框
       titleCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());  // 右边边框颜色
        
       titleCellStyle.setBorderTop(CellStyle.BORDER_THIN); // 上边边框
       titleCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());  // 上边边框颜色
           
//	                   titleCellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex()); // 前景色
//	                   titleCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); 
        return titleCellStyle;
	}
	
	
	/**
	 * 设置content样式
	 * @param workbook
	 * @return
	 */
	private CellStyle getContentCellStyle(Workbook workbook, CellStyle contentCellStyle) {
		contentCellStyle = workbook.createCellStyle();
    	 //设置样式
       Font font = workbook.createFont();
       font.setFontHeightInPoints((short) 10); //字体大小
       font.setFontName("宋体");
       
       contentCellStyle = workbook.createCellStyle();
       contentCellStyle.setFont(font);
       contentCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中
       contentCellStyle.setBorderBottom(CellStyle.BORDER_THIN); // 底部边框
       contentCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 底部边框颜色
        
       contentCellStyle.setBorderLeft(CellStyle.BORDER_THIN);  // 左边边框
       contentCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边边框颜色
        
       contentCellStyle.setBorderRight(CellStyle.BORDER_THIN); // 右边边框
       contentCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());  // 右边边框颜色
        
       contentCellStyle.setBorderTop(CellStyle.BORDER_THIN); // 上边边框
       contentCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());  // 上边边框颜色
        return contentCellStyle;
	}
	
	/**
	 * 获取类上面的注解描述
	 * @param model
	 * @return
	 */
	private String getClassModelDesc(Object model) {
		StringBuilder classifyName = new StringBuilder();
		Annotation[] clazzAnnotations = model.getClass().getAnnotations();
		if (clazzAnnotations != null && clazzAnnotations.length > 0) {
			for (int i = 0; i < clazzAnnotations.length; i++) {
				if (clazzAnnotations[i] instanceof ModelPropertyDesc) {
					ModelPropertyDesc mdclazzProDesc = (ModelPropertyDesc) clazzAnnotations[i];
					classifyName.replace(0, classifyName.length(), mdclazzProDesc.ModelDesc());
					break;
				}
			}
		}
		return classifyName.toString();
	}
	
	/**
	 * 获取属性上面注解值
	 * @param field
	 * @return
	 */
	private String getModelPropertyDesc(Field field) {
		StringBuilder titleName = new StringBuilder("");
		Annotation[] filedAnnotations = field.getAnnotations();
		//获取注解标识
		if (filedAnnotations != null && filedAnnotations.length > 0) {
			for (int j = 0; j < filedAnnotations.length; j++) {
				if (filedAnnotations[j] instanceof ModelPropertyDesc) {
					ModelPropertyDesc title = (ModelPropertyDesc) filedAnnotations[j];
					titleName.delete(0, titleName.length());
					//判断注解是否加了title标记
					if (title.ISExcelTitle()) {
						titleName.replace(0, titleName.length(), title.PropertyDesc());
					}
					break;
				}
			}
		}
		return titleName.toString();
	}
	
	 /**
     * 根据字段名称取值
     * @param obj
     * @param fieldName
     * @return
     */
    private Object getClassValue(Object obj, String fieldName) {
        if (obj == null) {
            return null;
        }
        try {
            Class<? extends Object> beanClass = obj.getClass();
            Method[] ms = beanClass.getMethods();
            for (int i = 0; i < ms.length; i++) {
                // 非get方法不取
                if (!ms[i].getName().startsWith("get")) {
                    continue;
                }
                Object objValue = null;
                try {
                    objValue = ms[i].invoke(obj, new Object[] {});
                } catch (Exception e) {
//                     logger.info("反射取值出错：" + e.toString());
                    continue;
                }
                if (objValue == null) {
                    continue;
                }
                if (ms[i].getName().toUpperCase().equals(fieldName.toUpperCase())
                        || ms[i].getName().substring(3).toUpperCase().equals(fieldName.toUpperCase())) {
                    return objValue;
                } else if (fieldName.toUpperCase().equals("SID")
                        && (ms[i].getName().toUpperCase().equals("ID") || ms[i].getName().substring(3).toUpperCase()
                                .equals("ID"))) {
                    return objValue;
                }
            }
        } catch (Exception e) {
            // logger.info("取方法出错！" + e.toString());
        }
        return null;
    }
	
    
    
    
    /**
	 * 生成输出流用于浏览器下载
	 * @param request
	 * @param response
	 * @param excelType
	 * @throws IOException 
	 */
	private void outputStream(Workbook workbook, HttpServletRequest request, 
			HttpServletResponse response, String excelType) throws IOException {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddhhmmsss");
		String filename = sdf.format(new Date()) + excelType;
		// 设置文件MIME类型
		response.reset(); // 必要地清除response中的缓存信息
		
		response.setContentType(request.getServletContext().getMimeType(filename));
//		response.setContentType("application/octet-stream");  
		response.setContentType("application/msexcel;charset=utf-8");  
        response.setHeader("name", filename);  
        response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");  
        response.setHeader("Pragma", "public");  
        response.setDateHeader("Expires", 0); 
        response.setHeader("Content-disposition","attachment; filename=\""+URLEncoder.encode(filename, "UTF-8")+ "\"");
		//网络下载代码
		workbook.write(response.getOutputStream());
		response.getOutputStream().flush();
		response.getOutputStream().close();
	}
    
	/**
	 * 多个实体放入listArray中，
	 * 按类别整合到一个sheet页导出
	 * @param <T>
	 * @param listArray
	 * @param fileType
	 * @return
	 */
	public <T> Workbook exportExcelMuti(List<T> listArray, String fileType){
		Workbook workbook = null;   //后缀xls excel2003
		if (StringUtils.isNoneBlank(fileType)) {
			if (fileType.contains(TYPE_XLS)) {
				workbook = new HSSFWorkbook();   //后缀xls excel2003
			} else if (fileType.contains(TYPE_XLSX)) {
				workbook = new XSSFWorkbook();   //后缀xlsx excel2007及高版本
			}
		} else {
			 workbook = new HSSFWorkbook(); 
		}
		/*这种写法很吊
		for (Sheet sheet : workbook) {
		}
		*/
		//设置sheet名称
		Sheet sheet = workbook.createSheet("导出数据");    
		CellStyle classifyCellStyle = getClassifyCellStyle(workbook, workbook.createCellStyle());
		CellStyle titleCellStyle = getTitleCellStyle(workbook, workbook.createCellStyle());
		CellStyle contentCellStyle = getContentCellStyle(workbook, workbook.createCellStyle());
		
		//判断是否为空
		if (listArray != null && listArray.size() > 0) {
			int rowIndex = 0;
			Row row = null;
			//遍历listArray集合 划分数据
			for (Object model : listArray) {
				//生成分类标识
				row = sheet.createRow((int) rowIndex++);
				String classifyName = getClassModelDesc(model);
				Cell classifyCell = row.createCell(0);
				classifyCell.setCellValue(classifyName);    
				classifyCell.setCellStyle(classifyCellStyle);
				//设置列宽--自适应
				sheet.setColumnWidth(0, classifyName.getBytes().length*2*200);
				
				
				//遍历 生成标题
				//==================标题栏============================
				//设置标题
				//创建新的row
				row = sheet.createRow((int) rowIndex++);
				row.setHeightInPoints(20);
				row.setHeight((short) 500);
				Field[] fields = model.getClass().getDeclaredFields();
				Cell titleCell = null;    
				if (fields != null && fields.length > 0) {
					int titleIndex = 0;
					for (Field field : fields) {
						//注解标识
						StringBuilder titleName = new StringBuilder("");
						//清空
						titleName.delete(0, titleName.length());
						//获取注解描述
						titleName.append(getModelPropertyDesc(field));
						//没有注解的不设置标题
						if (StringUtils.isNotBlank(titleName.toString())) {
							//有注解标识则创建单元格
							titleCell = row.createCell(titleIndex);    
							titleCell.setCellValue(titleName.toString());    
							titleCell.setCellStyle(titleCellStyle);    
							//设置列宽--自适应
							sheet.setColumnWidth(titleIndex++, titleName.toString().getBytes().length*2*200);
						}
					}
				}
				
				//遍历 生成内容
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				Cell contentCell = null;
				int dataIndex = 0;
				//设置行
				row = sheet.createRow(rowIndex++);
				row.setHeightInPoints(20);
				row.setHeight((short) 400);
				//设置列数据
				if (fields != null && fields.length > 0) {
					//依次取各字段的值
					for (Field field : fields) {
						//根据字段名称取值
						Object modelValue = getClassValue(model, field.getName());
						//有注解标识则创建单元格
						String modelProperty = getModelPropertyDesc(field);
						if (StringUtils.isNotBlank(modelProperty)) {
							contentCell = row.createCell(dataIndex++);
							//针对日期数据进行特别处理
			            	if (modelValue instanceof java.util.Date) {
			            		modelValue = sdf.format(modelValue);
			            	}
			            	if (modelValue instanceof java.sql.Date) {
			            		modelValue = sdf.format(modelValue);
			            	}
							contentCell.setCellValue((modelValue != null) ? modelValue.toString() : "");
							contentCell.setCellStyle(contentCellStyle);
						}
					}
				}
				//每个模块的数据间隔
				rowIndex += 3;
			}
			
		}
		return workbook;
	}
	
	
	/**
	 * 导出excel数据
	 * 目前只有一个sheet
	 * 需要导出的实体类字段要加 <br/>
	 * "@ModelPropertyDesc(PropertyDesc="字段描述", ISExcelTitle=false)" 注解 <br/>
	 * ISExcelTitle=false代表此列不出现在excel中
	 * 实体顺序和页面字段展示顺序一致即可导出和页面展示顺序一致的excel
	 * @param <T>
	 * 
	 * @param listArray
	 * @throws IOException
	 */
	public <T> Workbook exportExcel(List<T> listArray, String fileType) throws IOException {
		Workbook workbook = null;   //后缀xls excel2003
		if (StringUtils.isNoneBlank(fileType)) {
			if (fileType.contains(TYPE_XLS)) {
				workbook = new HSSFWorkbook();   //后缀xls excel2003
			} else if (fileType.contains(TYPE_XLSX)) {
				workbook = new XSSFWorkbook();   //后缀xlsx excel2007及高版本
			}
		} else {
			 workbook = new HSSFWorkbook(); 
		}
		
		//判断是否为空
		if (listArray != null && listArray.size() > 0) {
			//设置sheet名称
			Sheet sheet = workbook.createSheet(getClassModelDesc(listArray.get(0)));    
	        Row row = sheet.createRow((int) 0);    
	        row.setHeightInPoints(20);
			row.setHeight((short) 360);
			Object modelView = listArray.get(0);
			Field[] fields = modelView.getClass().getDeclaredFields();
			
			CellStyle titleCellStyle = getTitleCellStyle(workbook, workbook.createCellStyle());
			CellStyle contentCellStyle = getContentCellStyle(workbook, workbook.createCellStyle());
			
			//==================标题栏============================
			//设置标题
			Cell titleCell = null;    
			if (fields != null && fields.length > 0) {
				int titleIndex = 0;
				for (Field field : fields) {
					//注解标识
					StringBuilder titleName = new StringBuilder("");
					//清空
					titleName.delete(0, titleName.length());
					//获取注解描述
					titleName.append(getModelPropertyDesc(field));
					//没有注解的不设置标题
					if (StringUtils.isNotBlank(titleName.toString())) {
						//有注解标识则创建单元格
						titleCell = row.createCell(titleIndex);    
						titleCell.setCellValue(titleName.toString());    
						titleCell.setCellStyle(titleCellStyle);    
						//设置列宽--自适应
						sheet.setColumnWidth(titleIndex++, titleName.toString().getBytes().length*2*200);
					}
				}
			}

			//==================数据栏============================
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			Cell contentCell = null;    
			for (int i = 0; i < listArray.size(); i++) {
				//设置行
				row = sheet.createRow(i + 1);
				row.setHeight((short) 300);
				//设置列数据
				if (fields != null && fields.length > 0) {
					Object model = listArray.get(i);
					int dataIndex = 0;
					//依次取各字段的值
					for (Field field : fields) {
						//根据字段名称取值
						Object modelValue = getClassValue(model, field.getName());
						//有注解标识则创建单元格
						String modelProperty = getModelPropertyDesc(field);
						if (StringUtils.isNotBlank(modelProperty)) {
							contentCell = row.createCell(dataIndex++);
							//针对日期数据进行特别处理
			            	if (modelValue instanceof java.util.Date) {
			            		modelValue = sdf.format(modelValue);
			            	}
			            	if (modelValue instanceof java.sql.Date) {
			            		modelValue = sdf.format(modelValue);
			            	}
							contentCell.setCellValue((modelValue != null) ? modelValue.toString() : "");
							contentCell.setCellStyle(contentCellStyle);
						}
					}
				}
			}
		} else {
			//设置sheet名称
			workbook.createSheet("new sheet");    
		}
		return workbook;
	}
	
	/**
	 * 导出到指定文件夹
	 * @param request
	 * @param response
	 * @param listArray
	 * @param excelType
	 * @throws IOException
	 */
    public <T> void downloadExcelFile(HttpServletRequest request, HttpServletResponse response, 
			List<T> listArray, String excelType) throws IOException {
		//生成workbook对象
		Workbook workbook = exportExcel(listArray, excelType);
		//=========本地文件生成===========
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddhhmmsss");
		String filename = sdf.format(new Date()) + excelType;
		FileOutputStream fileOut = new FileOutputStream("D:/"+filename);
		workbook.write(fileOut);
		fileOut.close();
		workbook.close();
    }
    
    /**
   	 * 下载文件处理
   	 * @param request
   	 * @param response
   	 * @throws IOException
   	 */
   	public <T> void downloadExcel(HttpServletRequest request, HttpServletResponse response, 
   			Workbook workbook, String excelType) throws IOException {
   		outputStream(workbook, request, response, excelType);
   		workbook.close();
   	} 
    
    /**
	 * 下载文件处理
	 * 
	 * @param request
	 * @param response
	 * @throws IOException
	 */
	public <T> void downloadExcel(HttpServletRequest request, HttpServletResponse response, 
			List<T> listArray, String excelType) throws IOException {
		//生成workbook对象
		Workbook workbook = exportExcel(listArray, excelType);
		outputStream(workbook, request, response, excelType);
		workbook.close();
	}
	
	/**
	 *  下载文件处理
	 * @param request
	 * @param response
	 * @param listArray
	 * @param excelType
	 * @throws IOException
	 */
	public <T> void downloadExcelMuti(HttpServletRequest request, HttpServletResponse response, 
			List<T> listArray, String excelType) throws IOException {
		//生成workbook对象
		Workbook workbook = exportExcelMuti(listArray, excelType);
		outputStream(workbook, request, response, excelType);
		workbook.close();
	}
	
	
//***********************************************************
//	***********************************************************
//	*****************以下是测试代码请参考使用或测试*****************
//	***********************************************************
//	***********************************************************
	public static void testWorkbook() throws Exception {

		// 导出数据到excel
		Workbook wb = new HSSFWorkbook(); // xls格式的
//		FileOutputStream fileOut = new FileOutputStream("workbook.xls");
//		wb.write(fileOut);
//		fileOut.close();
//		wb.close();

//		Workbook wb2 = new XSSFWorkbook();
//		FileOutputStream fileOut2 = new FileOutputStream("workbook.xlsx");
//		wb2.write(fileOut2);
//		fileOut2.close();
//		wb2.close();
		
		
//		Workbook wb = new HSSFWorkbook();  // or new XSSFWorkbook();
//	    Sheet sheet1 = wb.createSheet("第一个sheet");
//	    Sheet sheet2 = wb.createSheet("第二个sheet");

	    // Note that sheet name is Excel must not exceed 31 characters
	    // and must not contain any of the any of the following characters:
	    // 0x0000
	    // 0x0003
	    // colon (:)
	    // backslash (\)
	    // asterisk (*)
	    // question mark (?)
	    // forward slash (/)
	    // opening square bracket ([)
	    // closing square bracket (])

	    // You can use org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)}
	    // for a safe way to create valid names, this utility replaces invalid characters with a space (' ')
	    String safeName = WorkbookUtil.createSafeSheetName("[第三个sheet*?]"); // returns " O'Brien's sales   "
	    Sheet sheet3 = wb.createSheet(safeName);
	    wb.close();
//	    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
//	    wb.write(fileOut);
//	    fileOut.close();
		//Create Blank workbook
//	      XSSFWorkbook workbook = new XSSFWorkbook(); 
//	      //Create file system using specific name
//	      FileOutputStream out = new FileOutputStream(
//				new File("createworkbook.xlsx"));
//	      //write operation workbook using file out object 
//	      workbook.write(out);
//	      out.close();
//	      System.out.println("createworkbook.xlsx written successfully");
//	      workbook.close();
	
		//Creating Cells
	    CreationHelper createHelper = wb.getCreationHelper();
	    Sheet sheet = wb.createSheet("新的cell");

	    // Create a row and put some cells in it. Rows are 0 based.
	    Row row = sheet.createRow((short)0);
	    // Create a cell and put a value in it.
	    Cell cell = row.createCell(0);
	    cell.setCellValue(1);

	    // Or do it on one line.
	    row.createCell(1).setCellValue(1.2);
	    row.createCell(2).setCellValue(
	         createHelper.createRichTextString("This is a string"));
	    row.createCell(3).setCellValue(true);

	    // Write the output to a file
//	    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
//	    wb.write(fileOut);
//	    fileOut.close();
	    
//	    CreationHelper createHelper = wb.getCreationHelper();
	    Sheet sheet4 = wb.createSheet("new sheet2");

	    // Create a row and put some cells in it. Rows are 0 based.
	    Row row1 = sheet4.createRow(0);

	    // Create a cell and put a date value in it.  The first cell is not styled
	    // as a date.
	    Cell cell1 = row1.createCell(0);
	    cell1.setCellValue(new Date());

	    // we style the second cell as a date (and time).  It is important to
	    // create a new cell style from the workbook otherwise you can end up
	    // modifying the built in style and effecting not only this cell but other cells.
	    CellStyle cellStyle = wb.createCellStyle();
	    cellStyle.setDataFormat(
	        createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
	    cell1 = row1.createCell(1);
	    cell1.setCellValue(new Date());
	    cell1.setCellStyle(cellStyle);

	    //you can also set date as java.util.Calendar
	    cell1 = row1.createCell(2);
	    cell1.setCellValue(Calendar.getInstance());
	    cell1.setCellStyle(cellStyle);

	    // Write the output to a file
	    
	    //Working with different types of cells
	    Workbook wb_different = new HSSFWorkbook();
	    Sheet sheet_different = wb_different.createSheet("新的 sheet");
	    Row row_dif = sheet_different.createRow((short)0);
	    row_dif.createCell(0).setCellValue(1.1111111111111111111111);
	    row_dif.createCell(1).setCellValue(new Date());
	    row_dif.createCell(2).setCellValue(Calendar.getInstance());
	    row_dif.createCell(3).setCellValue("字符串");
	    row_dif.createCell(4).setCellValue(true);
	    row_dif.createCell(5).setCellType(CellType.GREATER_THAN);

	    // Write the output to a file
	    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
	    wb_different.write(fileOut);
	    fileOut.close();
		
	}
	
	/**
     * 对外提供读取excel 的方法
     * */
    public static List<List<Object>> readExcel(File file) throws IOException {
        String fileName = file.getName();
        String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName
                .substring(fileName.lastIndexOf(".") + 1);
        if ("xls".equals(extension)) {
            return read2003Excel(file);
        } else if ("xlsx".equals(extension)) {
            return read2007Excel(file);
        } else {
            throw new IOException("不支持的文件类型");
        }
    }

    /**
     * 读取 office 2003 excel
     * 
     * @throws IOException
     * @throws FileNotFoundException
     */
    private static List<List<Object>> read2003Excel(File file)
            throws IOException {
        List<List<Object>> list = new LinkedList<List<Object>>();
        HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));
        HSSFSheet sheet = hwb.getSheetAt(0);
        Object value = null;
        HSSFRow row = null;
        HSSFCell cell = null;
        int counter = 0;
        for (int i = sheet.getFirstRowNum(); counter < sheet
                .getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            } else {
                counter++;
            }
            List<Object> linked = new LinkedList<Object>();
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                cell = row.getCell(j);
                if (cell == null) {
                    continue;
                }
                DecimalFormat df = new DecimalFormat("0");// 格式化 number String
                                                            // 字符
                SimpleDateFormat sdf = new SimpleDateFormat(
                        "yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
                DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
                switch (cell.getCellType()) {
                case XSSFCell.CELL_TYPE_STRING:
                //  System.out.println(i + "行" + j + " 列 is String type");
                    value = cell.getStringCellValue();
                    break;
                case XSSFCell.CELL_TYPE_NUMERIC:
                    /*System.out.println(i + "行" + j
                            + " 列 is Number type ; DateFormt:"
                            + cell.getCellStyle().getDataFormatString());*/
                    if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                        value = df.format(cell.getNumericCellValue());
                    } else if ("General".equals(cell.getCellStyle()
                            .getDataFormatString())) {
                        value = nf.format(cell.getNumericCellValue());
                    } else {
                        value = sdf.format(HSSFDateUtil.getJavaDate(cell
                                .getNumericCellValue()));
                    }
                    break;
                case XSSFCell.CELL_TYPE_BOOLEAN:
                //  System.out.println(i + "行" + j + " 列 is Boolean type");
                    value = cell.getBooleanCellValue();
                    break;
                case XSSFCell.CELL_TYPE_BLANK:
                //  System.out.println(i + "行" + j + " 列 is Blank type");
                    value = "";
                    break;
                default:
                //  System.out.println(i + "行" + j + " 列 is default type");
                    value = cell.toString();
                }
                if (value == null || "".equals(value)) {
                    continue;
                }
                linked.add(value);
            }
            list.add(linked);
        }
        hwb.close();
        return list;
    }

    /**
     * 读取Office 2007 excel
     * */
    private static List<List<Object>> read2007Excel(File file)
            throws IOException {
        List<List<Object>> list = new LinkedList<List<Object>>();
        // 构造 XSSFWorkbook 对象，strPath 传入文件路径
        XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
        // 读取第一章表格内容
        XSSFSheet sheet = xwb.getSheetAt(0);
        Object value = null;
        XSSFRow row = null;
        XSSFCell cell = null;
        int counter = 0;
        for (int i = sheet.getFirstRowNum(); counter < sheet
                .getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            } else {
                counter++;
            }
            List<Object> linked = new LinkedList<Object>();
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                cell = row.getCell(j);
                if (cell == null) {
                    continue;
                }
                DecimalFormat df = new DecimalFormat("0");// 格式化 number String
                                                            // 字符
                SimpleDateFormat sdf = new SimpleDateFormat(
                        "yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
                DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
                DecimalFormat dfMobile = new DecimalFormat("#"); //治疗手机号大数据带E的问题 
                switch (cell.getCellType()) {
                case XSSFCell.CELL_TYPE_STRING:
//                    System.out.println(i + "行" + j + " 列 is String type");
                    value = cell.getStringCellValue();
                    break;
                case XSSFCell.CELL_TYPE_NUMERIC:
                  System.out.println(i + "行" + j
                            + " 列 is Number type ; DateFormt:"
                            + cell.getCellStyle().getDataFormatString());
                    if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                        value = df.format(cell.getNumericCellValue());
                    } else if ("General".equals(cell.getCellStyle()
                            .getDataFormatString())) {
//                        value = nf.format(cell.getNumericCellValue());
                    	double num = cell.getNumericCellValue();
                    	if ((num+"").length() >= 10) {
                    		value = dfMobile.format(num);
                    	} else {
                    		value = String.valueOf(num);    
                    	}
                    } else if (cell.getCellStyle().getDataFormatString().endsWith("%") || 
                    		cell.getCellStyle().getDataFormatString().contains(".0")){
                    	value = cell.getNumericCellValue() * 100 + "%";
                    } else if (HSSFDateUtil.isCellDateFormatted(cell)){
                    	value = sdf.format(HSSFDateUtil.getJavaDate(cell
                                .getNumericCellValue()));
                    } else {
                    	value = cell.getNumericCellValue();
                    }
                    System.out.println(value);
                    break;
                case XSSFCell.CELL_TYPE_BOOLEAN:
//                  System.out.println(i + "行" + j + " 列 is Boolean type");
                    value = cell.getBooleanCellValue();
                    break;
                case XSSFCell.CELL_TYPE_BLANK:
//                  System.out.println(i + "行" + j + " 列 is Blank type");
                    value = "";
                    break;
                default:
//                  System.out.println(i + "行" + j + " 列 is default type");
                    value = cell.toString();
                }
                if (value == null || "".equals(value)) {
//                    continue;
                	value = cell.toString();
                }
                linked.add(value);
            }
            list.add(linked);
        }
        xwb.close();
        return list;
    }
	
	
	
	
	///=============================以下是测试方法========================================////
	public static void main(String[] args) throws IOException {/*
		Workbook wb = new HSSFWorkbook();
	    Sheet sheet = wb.createSheet("新的sheet");
	    
	    org.apache.poi.ss.usermodel.Header header = sheet.getHeader();
	    header.setCenter("Center Header");
	    header.setLeft("Left Header");
	    header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") +
	                    HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italic font and size 16");

	    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
	    wb.write(fileOut);
	    fileOut.close();
		
	*/
		
		List<BondTrialInfoView> trialInfoViews = new ArrayList<BondTrialInfoView>();
		BondTrialInfoView bondTrialInfoView = new BondTrialInfoView();
		bondTrialInfoView.setAcceptTime(new Date());
		bondTrialInfoView.setAccountingName("会计师事务所名称xxxx");
		bondTrialInfoView.setAccountingSignatory("会计师事务所签字会计师xxx");
		bondTrialInfoView.setApprovalNum("证监会核准文号XXX");
		bondTrialInfoView.setApprovalTime(new Date());
		bondTrialInfoView.setBaseId(1000);
		bondTrialInfoView.setBeIssueAmount(20L);
		bondTrialInfoView.setBondCode("债券代码XXX");
//		bondTrialInfoView.setShortName("债券简称xxx");
		bondTrialInfoView.setBondName("债券名称xxx");
		bondTrialInfoView.setCheckState("审核中");
		bondTrialInfoView.setCreditMeasures("增信措施XXX");
		bondTrialInfoView.setExpertTime(new Date());
		bondTrialInfoView.setFirstFeedbackTime(new Date());
		bondTrialInfoView.setFirstReplayTime(new Date());
		bondTrialInfoView.setId(123);
		bondTrialInfoView.setIssueAmount(6000);
		bondTrialInfoView.setIssuerBusiness("主营业务XXX");
		bondTrialInfoView.setIssuerCity("注册地(市)XXX");
		bondTrialInfoView.setIssuerContactWay("发行人联系方式XXXX");
		bondTrialInfoView.setIssuerFinancial("是否为非金融企业");
		bondTrialInfoView.setIssuerIndustry("所属行业XXX");
		bondTrialInfoView.setIssuerName("发行人名称XXX0---王五");
		bondTrialInfoView.setIssuerNature("企业性质XXX");
		bondTrialInfoView.setIssuerProvince("注册地(省份)XXX");
		bondTrialInfoView.setIssuerRating("主体评级XCCCC");
		bondTrialInfoView.setIssuerSRB("发行人所属证监局XXX");
		bondTrialInfoView.setIssueWay("发行方式XXX");
		bondTrialInfoView.setLawName("律师事务所名称XXX");
		bondTrialInfoView.setLawSignatory("律师事务所签字律师XXXXX");
		bondTrialInfoView.setPreAuditTime(new Date());
		bondTrialInfoView.setRating("债项评级XXXX");
		bondTrialInfoView.setRatingAgencyCharge("资信评级机构项目负责人XXXXX");
		bondTrialInfoView.setRatingAgencyName("资信评级机构XXXX");
		bondTrialInfoView.setRatingAgencySRB("资信评级机构所属证监局XXX证监局xxxx");
		bondTrialInfoView.setRemark("备注");
		bondTrialInfoView.setSecondFeedbackTime(new Date());
		bondTrialInfoView.setSecondReplayTime(new Date());
		bondTrialInfoView.setState(1);
		bondTrialInfoView.setStockCode("股票代码(如有)XXXX");
		bondTrialInfoView.setTrem(1111);
		bondTrialInfoView.setTrusteeName("受托管理人名称XXXX");
		bondTrialInfoView.setTrusteeSRB("受托管理人所属证监局XXX");
		bondTrialInfoView.setUnderwriterCharge("主承销商项目负责人XXX");
		bondTrialInfoView.setUnderwriterName("主承销商名称XXX");
		bondTrialInfoView.setUnderwriterSRB("主承销商所属证监局XXXX");
		bondTrialInfoView.setUserId(111);
		
		
		trialInfoViews.add(bondTrialInfoView);
		trialInfoViews.add(bondTrialInfoView);
		trialInfoViews.add(bondTrialInfoView);
		trialInfoViews.add(bondTrialInfoView);
		trialInfoViews.add(bondTrialInfoView);
		trialInfoViews.add(bondTrialInfoView);
		trialInfoViews.add(bondTrialInfoView);
		trialInfoViews.add(bondTrialInfoView);
		
		
		
		
		
//		BondBaseInfo bondBaseInfo = new BondBaseInfo();
		
		List<Object> data = new ArrayList<Object>();
		data.add(bondTrialInfoView);
//		data.add(bondBaseInfo);
		data.add(bondTrialInfoView);
		data.add(bondTrialInfoView);
		
		
		Workbook workbook = getInstance().exportExcel(trialInfoViews, ".xls");
//		Workbook workbook = getInstance().exportExcelMuti(data, ".xls");
		FileOutputStream fileOut = new FileOutputStream("workbook.xls");
		workbook.write(fileOut);
		fileOut.close();
	
	}
	
	public static List<BondTrialInfoView>  getData() {
		List<BondTrialInfoView> trialInfoViews = new ArrayList<BondTrialInfoView>();
		BondTrialInfoView bondTrialInfoView = new BondTrialInfoView();
		bondTrialInfoView.setAcceptTime(new Date());
		bondTrialInfoView.setAccountingName("会计师事务所名称xxxx");
		bondTrialInfoView.setAccountingSignatory("会计师事务所签字会计师xxx");
		bondTrialInfoView.setApprovalNum("证监会核准文号XXX");
		bondTrialInfoView.setApprovalTime(new Date());
		bondTrialInfoView.setBaseId(1000);
		bondTrialInfoView.setBeIssueAmount(20L);
		bondTrialInfoView.setBondCode("债券代码XXX");
		bondTrialInfoView.setBondName("债券名称xxx");
//		bondTrialInfoView.setShortName("债券简称xxx");
		bondTrialInfoView.setCheckState("审核中");
		bondTrialInfoView.setCreditMeasures("增信措施XXX");
		bondTrialInfoView.setExpertTime(new Date());
		bondTrialInfoView.setFirstFeedbackTime(new Date());
		bondTrialInfoView.setFirstReplayTime(new Date());
		bondTrialInfoView.setId(123);
		bondTrialInfoView.setIssueAmount(6000);
		bondTrialInfoView.setIssuerBusiness("主营业务XXX");
		bondTrialInfoView.setIssuerCity("注册地(市)XXX");
		bondTrialInfoView.setIssuerContactWay("发行人联系方式XXXX");
		bondTrialInfoView.setIssuerFinancial("是否为非金融企业");
		bondTrialInfoView.setIssuerIndustry("所属行业XXX");
		bondTrialInfoView.setIssuerName("发行人名称XXX0---王五");
		bondTrialInfoView.setIssuerNature("企业性质XXX");
		bondTrialInfoView.setIssuerProvince("注册地(省份)XXX");
		bondTrialInfoView.setIssuerRating("主体评级XCCCC");
		bondTrialInfoView.setIssuerSRB("发行人所属证监局XXX");
		bondTrialInfoView.setIssueWay("发行方式XXX");
		bondTrialInfoView.setLawName("律师事务所名称XXX");
		bondTrialInfoView.setLawSignatory("律师事务所签字律师XXXXX");
		bondTrialInfoView.setPreAuditTime(new Date());
		bondTrialInfoView.setRating("债项评级XXXX");
		bondTrialInfoView.setRatingAgencyCharge("资信评级机构项目负责人XXXXX");
		bondTrialInfoView.setRatingAgencyName("资信评级机构XXXX");
		bondTrialInfoView.setRatingAgencySRB("资信评级机构所属证监局XXX证监局xxxx");
		bondTrialInfoView.setRemark("备注");
		bondTrialInfoView.setSecondFeedbackTime(new Date());
		bondTrialInfoView.setSecondReplayTime(new Date());
		bondTrialInfoView.setState(1);
		bondTrialInfoView.setStockCode("股票代码(如有)XXXX");
		bondTrialInfoView.setTrem(1111);
		bondTrialInfoView.setTrusteeName("受托管理人名称XXXX");
		bondTrialInfoView.setTrusteeSRB("受托管理人所属证监局XXX");
		bondTrialInfoView.setUnderwriterCharge("主承销商项目负责人XXX");
		bondTrialInfoView.setUnderwriterName("主承销商名称XXX");
		bondTrialInfoView.setUnderwriterSRB("主承销商所属证监局XXXX");
		bondTrialInfoView.setUserId(111);
		
		
		trialInfoViews.add(bondTrialInfoView);
		trialInfoViews.add(bondTrialInfoView);
		trialInfoViews.add(bondTrialInfoView);
		trialInfoViews.add(bondTrialInfoView);
		trialInfoViews.add(bondTrialInfoView);
		trialInfoViews.add(bondTrialInfoView);
		trialInfoViews.add(bondTrialInfoView);
		trialInfoViews.add(bondTrialInfoView);
		return trialInfoViews;
	}
	
	

	
}
